﻿Imports NHibernate
Imports FluentNHibernate.Cfg
Imports FluentNHibernate.Cfg.Db
Imports NHibernate.Cfg
Imports NHibernate.Tool.hbm2ddl
Imports System.Reflection
Imports NHibernate.Linq
Imports System.Collections.ObjectModel
Imports NHibernate.Mapping
Imports FluentNHibernate.Conventions
Imports FluentNHibernate.Conventions.Helpers
Imports FluentNHibernate.Conventions.Inspections
Imports System.Windows.Threading
Imports System.IO

Public Class FluentNHibernateConnector
    Implements IDatabase

    Private _appid As String
    Private sessionFactory As ISessionFactory
    Private _isconnected = False
    Private _company As String
    Private _dp As Dispatcher
    Private _type As String
    Private _directorypath As String

    Dim dbServer, dbUsername, dbName, dbPassword As String
    ''' <summary>
    ''' Creates a new instance of the class
    ''' </summary>
    ''' <param name="appid">Unique App-ID</param>
    ''' <param name="_dbServer">Serveraddress</param>
    ''' <param name="_dbName">Databasename</param>
    ''' <param name="_dbUsername">Database-Username</param>
    ''' <param name="_dbPasswd">Database-Password</param>
    ''' <remarks></remarks>
    Public Sub New(appid As String, _dbServer As String, _dbName As String, _dbUsername As String, _dbPasswd As String, dp As Dispatcher, type As String)
        _type = type
        _appid = appid
        dbServer = _dbServer
        dbUsername = _dbUsername
        dbName = _dbName
        dbPassword = _dbPasswd
        _dp = dp
    End Sub

    ''' <summary>
    ''' Creates a new instance of the class
    ''' </summary>
    ''' <param name="appid"></param>
    ''' <param name="directory"></param>
    ''' <param name="dp"></param>
    ''' <param name="type"></param>
    ''' <remarks></remarks>
    Public Sub New(appid As String, directory As String, dp As Dispatcher, type As String)
        _type = type
        _appid = appid
        _dp = dp
        _directorypath = directory
    End Sub

    Public Sub Connect() Implements IDatabase.Connect
        If String.IsNullOrEmpty(_type) Then
            RaiseEvent DatabaseError("Not valid database type set")
            Exit Sub
        End If
        Threading.Tasks.Task.Factory.StartNew(Sub()
                                                  Try
                                                      sessionFactory = CreateSessionFactory()

                                                      Dim company As String
                                                      Using session = sessionFactory.OpenSession()
                                                          Using transaction = session.BeginTransaction()
                                                              Try
                                                                  company = session.Get(Of nHibernate.Models.ApplicationSettingsModel)((_appid + "-LastCompany").ToString).Value()
                                                              Catch ex As Exception
                                                                  company = ""
                                                              End Try
                                                          End Using
                                                      End Using
                                                      If Not String.IsNullOrEmpty(company) Then
                                                          _company = company
                                                          sessionFactory = MySQLCreateSessionFactoryWithCompany()
                                                      End If

                                                      _isconnected = True
                                                      _dp.BeginInvoke(Sub()
                                                                          RaiseEvent DatabaseConnected(Me)
                                                                      End Sub)

                                                  Catch ex As Exception
                                                      Log.AddError(ex.InnerException.ToString + " -> FluentNHibernateConnector")
                                                      _isconnected = False
                                                      _dp.BeginInvoke(Sub()
                                                                          RaiseEvent DatabaseNotConnected(Me)
                                                                          RaiseEvent DatabaseError(ex.InnerException.ToString)
                                                                      End Sub)
                                                  End Try
                                              End Sub)
    End Sub

    Private Function CreateSessionFactory() As ISessionFactory
        Select Case _type
            Case "MySQL"
                Return MySQLCreateSessionFactory()
            Case "MsSQL2008"
                Return MsSQL2008CreateSessionFactory()
            Case "MsSQL2012"
                Return MsSQL2012CreateSessionFactory()
            Case "PostgreSQL"
                Return PostgreSQLCreateSessionFactory()
            Case "DB2"
                Return DB2CreateSessionFactory()
            Case "SQLite"
                Return SQLiteCreateSessionFactory()
        End Select
        Return Nothing
    End Function

    Private Function CreateSessionFactoryWithCompany() As ISessionFactory
        Select Case _type
            Case "MySQL"
                Return MySQLCreateSessionFactoryWithCompany()
            Case "MsSQL2008"
                Return MsSQL2008CreateSessionFactoryWithCompany()
            Case "MsSQL2012"
                Return MsSQL2012CreateSessionFactoryWithCompany()
            Case "PostgreSQL"
                Return PostgreSQLCreateSessionFactoryWithCompany()
            Case "DB2"
                Return DB2CreateSessionFactoryWithCompany()
            Case "SQLite"
                Return SQLiteCreateSessionFactoryWithCompany()
        End Select
        Return Nothing
    End Function

    Public ReadOnly Property IsConnected As Boolean Implements IDatabase.IsConnected
        Get
            Return _isconnected
        End Get
    End Property

    Private Function SetTablePrefix(c As IClassInspector) As String
        Dim li As New List(Of String)
        li.Add("AddressModel") : li.Add("EAddressModel") : li.Add("DeliveryAddressModel") : li.Add("ContactModel")
        li.Add("ArticleModel") : li.Add("Category") : li.Add("CustomerModel") : li.Add("OrderedArticleModel") : li.Add("OrderModel")
        Dim tablecompany As String = _company
        While tablecompany.Contains(" ")
            tablecompany = tablecompany.Replace(" ", "")
        End While
        While tablecompany.Contains("-")
            tablecompany = tablecompany.Replace("-", "")
        End While

        If li.Contains(c.EntityType.Name) Then
            Return tablecompany + "_" + c.EntityType.Name
        End If
        Return c.EntityType.Name
    End Function

    Private Function MySQLCreateSessionFactoryWithCompany() As ISessionFactory
        Dim config = Fluently.Configure().Database(MySQLConfiguration.Standard.ConnectionString(Function(cs) cs.Server(dbServer).Database(dbName).Username(dbUsername).Password(dbPassword))) _
                     .Mappings(Function(x) x.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly) _
                     .Conventions.Add(FluentNHibernate.Conventions.Helpers.Table.Is(AddressOf SetTablePrefix))) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function MySQLCreateSessionFactory() As ISessionFactory
        Dim config = Fluently.Configure().Database(MySQLConfiguration.Standard.ConnectionString(Function(cs) cs.Server(dbServer).Database(dbName).Username(dbUsername).Password(dbPassword))) _
            .Mappings(Function(x) x.FluentMappings.Add(Of UserPreviewMap).Add(Of CompanySettingsMap).Add(Of ApplicationSettingsMap).Add(Of PaymentMethodeMap)()) _
            .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function SQLiteCreateSessionFactoryWithCompany() As ISessionFactory
        Dim config = Fluently.Configure().Database(SQLiteConfiguration.Standard.UsingFile(_directorypath + "\billerdb.db")) _
                     .Mappings(Function(x) x.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly) _
                     .Conventions.Add(FluentNHibernate.Conventions.Helpers.Table.Is(AddressOf SetTablePrefix))) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function SQLiteCreateSessionFactory() As ISessionFactory
        Dim config = Fluently.Configure().Database(SQLiteConfiguration.Standard.UsingFile(_directorypath + "billerdb.db")) _
                     .Mappings(Function(x) x.FluentMappings.Add(Of UserPreviewMap).Add(Of CompanySettingsMap).Add(Of ApplicationSettingsMap).Add(Of PaymentMethodeMap)()) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function MsSQL2008CreateSessionFactoryWithCompany() As ISessionFactory
        Dim config = Fluently.Configure().Database(MsSqlConfiguration.MsSql2008.ConnectionString(Function(cs) cs.Server(dbServer).Database(dbName).Username(dbUsername).Password(dbPassword))) _
                     .Mappings(Function(x) x.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly) _
                     .Conventions.Add(FluentNHibernate.Conventions.Helpers.Table.Is(AddressOf SetTablePrefix))) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function MsSQL2008CreateSessionFactory() As ISessionFactory
        Dim config = Fluently.Configure().Database(MsSqlConfiguration.MsSql2008.ConnectionString(Function(cs) cs.Server(dbServer).Database(dbName).Username(dbUsername).Password(dbPassword))) _
                     .Mappings(Function(x) x.FluentMappings.Add(Of UserPreviewMap).Add(Of CompanySettingsMap).Add(Of ApplicationSettingsMap).Add(Of PaymentMethodeMap)()) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function MsSQL2012CreateSessionFactoryWithCompany() As ISessionFactory
        Dim config = Fluently.Configure().Database(MsSqlConfiguration.MsSql2012.ConnectionString(Function(cs) cs.Server(dbServer).Database(dbName).Username(dbUsername).Password(dbPassword))) _
                     .Mappings(Function(x) x.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly) _
                     .Conventions.Add(FluentNHibernate.Conventions.Helpers.Table.Is(AddressOf SetTablePrefix))) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function MsSQL2012CreateSessionFactory() As ISessionFactory
        Dim config = Fluently.Configure().Database(MsSqlConfiguration.MsSql2012.ConnectionString(Function(cs) cs.Server(dbServer).Database(dbName).Username(dbUsername).Password(dbPassword))) _
                     .Mappings(Function(x) x.FluentMappings.Add(Of UserPreviewMap).Add(Of CompanySettingsMap).Add(Of ApplicationSettingsMap).Add(Of PaymentMethodeMap)()) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function PostgreSQLCreateSessionFactoryWithCompany() As ISessionFactory
        Dim config = Fluently.Configure().Database(PostgreSQLConfiguration.Standard.ConnectionString(Function(cs) cs.Host(dbServer).Database(dbName).Username(dbUsername).Password(dbPassword))) _
                     .Mappings(Function(x) x.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly) _
                     .Conventions.Add(FluentNHibernate.Conventions.Helpers.Table.Is(AddressOf SetTablePrefix))) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function PostgreSQLCreateSessionFactory() As ISessionFactory
        Dim config = Fluently.Configure().Database(PostgreSQLConfiguration.Standard.ConnectionString(Function(cs) cs.Host(dbServer).Database(dbName).Username(dbUsername).Password(dbPassword))) _
                     .Mappings(Function(x) x.FluentMappings.Add(Of UserPreviewMap).Add(Of CompanySettingsMap).Add(Of ApplicationSettingsMap).Add(Of PaymentMethodeMap)()) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function DB2CreateSessionFactoryWithCompany() As ISessionFactory
        Dim config = Fluently.Configure().Database(DB2Configuration.Standard.ConnectionString(Function(cs) cs.Server(dbServer).Database(dbName).Username(dbUsername).Password(dbPassword))) _
                     .Mappings(Function(x) x.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly) _
                     .Conventions.Add(FluentNHibernate.Conventions.Helpers.Table.Is(AddressOf SetTablePrefix))) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Function DB2CreateSessionFactory() As ISessionFactory
        Dim config = Fluently.Configure().Database(DB2Configuration.Standard.ConnectionString(Function(cs) cs.Server(dbServer).Database(dbName).Username(dbUsername).Password(dbPassword))) _
                     .Mappings(Function(x) x.FluentMappings.Add(Of UserPreviewMap).Add(Of CompanySettingsMap).Add(Of ApplicationSettingsMap).Add(Of PaymentMethodeMap)()) _
                     .ExposeConfiguration(AddressOf BuildSchema)
        Return config.BuildSessionFactory()
    End Function

    Private Sub BuildSchema(config As Configuration)
        Try
            Dim t As New SchemaUpdate(config)
            t.Execute(True, True)
        Catch : End Try
    End Sub

    Public Function ArticleExists(ArticleID As String, usr As User) As Boolean Implements IDatabase.ArticleExists
        Try
            If usr.CanReadArticle = False Then
                RaiseEvent UserRightsMissing(User.ArticleReadDenied)
                Return False
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.ArticleReadDenied)
            Return False
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Return Not IsNothing(session.Get(Of nHibernate.Models.ArticleModel)(ArticleID))
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return False
    End Function

    Public Function CustomerExists(CustomerID As String, usr As User) As Boolean Implements IDatabase.CustomerExists
        Try
            If usr.CanReadCustomer = False Then
                RaiseEvent UserRightsMissing(User.CustomerReadDenied)
                Return False
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.CustomerReadDenied)
            Return False
        End Try
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Return Not IsNothing(session.Get(Of nHibernate.Models.CustomerModel)(CustomerID))
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return False
    End Function

    Public Sub DeleteArticle(ArticleID As String, usr As User) Implements IDatabase.DeleteArticle
        Try
            If usr.CanWriteArticle = False Then
                RaiseEvent UserRightsMissing(User.ArticleWriteDenied)
                Exit Sub
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.ArticleWriteDenied)
            Exit Sub
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.Delete(session.Get(Of nHibernate.Models.ArticleModel)(ArticleID))
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Sub DeleteCustomer(CustomerID As String, usr As User) Implements IDatabase.DeleteCustomer
        Try
            If usr.CanWriteCustomer = False Then
                RaiseEvent UserRightsMissing(User.CustomerWriteDenied)
                Exit Sub
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.CustomerWriteDenied)
            Exit Sub
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Dim c = session.Get(Of nHibernate.Models.CustomerModel)(CustomerID)
                    session.Delete(c)
                    Dim a = From ea In session.Query(Of nHibernate.Models.EAddressModel)() Where ea.EAddressID.StartsWith(c.CustomerID + "-")
                    For Each item In a
                        session.Delete(item)
                    Next
                    session.Delete(c.Address)
                    session.Delete(c.Contact)
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Sub DeleteOrder(OrderID As String, type As Dokumentart, usr As User) Implements IDatabase.DeleteOrder
        Try
            If usr.CanWriteOrder = False Then
                RaiseEvent UserRightsMissing(User.OrderWriteDenied)
                Exit Sub
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.OrderWriteDenied)
            Exit Sub
        End Try

        Using session = sessionFactory.OpenSession()
            Using transaction = session.BeginTransaction()
                Dim o = session.Get(Of nHibernate.Models.OrderModel)(CInt(type).ToString + "-" + OrderID)
                session.Delete(o)
                Dim a = From ea In session.Query(Of nHibernate.Models.OrderedArticleModel)() Where ea.OrderArticleIDWithOrderID.StartsWith(o.UniqueID + "-")
                For Each item In a
                    session.Delete(item)
                Next
                If Not IsNothing(o.DeliveryAddress) Then
                    session.Delete(o.DeliveryAddress)
                End If
                transaction.Commit()
            End Using
        End Using
    End Sub

    Public Function GetAllArticles(usr As User) As IList(Of ArticlePreviewModel) Implements IDatabase.GetAllArticles
        Try
            If usr.CanReadArticle = False Then
                RaiseEvent UserRightsMissing(User.ArticleReadDenied)
                Return New ObservableCollection(Of ArticlePreviewModel)
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.ArticleReadDenied)
            Return New ObservableCollection(Of ArticlePreviewModel)
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Dim c = From articles In session.Query(Of nHibernate.Models.ArticleModel)() Select articles.ArticleID, articles.ArticleDescription, articles.ArticleUnit, articles.ArticlePrice1Amount, articles.ArticlePrice1Currency, articles.ArticlePrice1IsGross, articles.ArticleCategory
                    Dim output As New ObservableCollection(Of ArticlePreviewModel)
                    For Each item In c
                        output.Add(New ArticlePreviewModel With {.ArticleID = item.ArticleID, .ArticleDescription = item.ArticleDescription, .ArticleUnit = item.ArticleUnit.Name, .ArticlePrice = New EMoney(item.ArticlePrice1Amount, item.ArticlePrice1IsGross, item.ArticlePrice1Currency), .Category = item.ArticleCategory})
                    Next
                    Return output
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return New ObservableCollection(Of ArticlePreviewModel)
    End Function

    Public Function GetAllCustomers(usr As User) As IList(Of CustomerPreviewModel) Implements IDatabase.GetAllCustomers
        Try
            If usr.CanReadCustomer = False Then
                RaiseEvent UserRightsMissing(User.CustomerReadDenied)
                Return New ObservableCollection(Of CustomerPreviewModel)
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.CustomerReadDenied)
            Return New ObservableCollection(Of CustomerPreviewModel)
        End Try
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Dim c = From customers In session.Query(Of nHibernate.Models.CustomerModel)() Select customers.CustomerID, customers.DisplayName, customers.Address
                    Dim output As New ObservableCollection(Of CustomerPreviewModel)
                    For Each item In c
                        output.Add(New CustomerPreviewModel With {.Address = item.Address.ToString, .CustomerID = item.CustomerID, .DisplayedName = item.DisplayName})
                    Next
                    Return output
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try

        Return New ObservableCollection(Of CustomerPreviewModel)
    End Function

    Public Function GetAllOrders(usr As User) As IList(Of OrderPreviewModel) Implements IDatabase.GetAllOrders
        Try
            If usr.CanReadOrder = False Then
                RaiseEvent UserRightsMissing(User.OrderReadDenied)
                Return New ObservableCollection(Of OrderPreviewModel)
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.OrderReadDenied)
            Return New ObservableCollection(Of OrderPreviewModel)
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Dim c = From orders In session.Query(Of nHibernate.Models.OrderModel)() Select orders.CustomerNamePreview, orders.DokumentArt, orders.OrderID, orders.TotalValueAmount, orders.TotalValueCurrency, orders.Datum, orders.Customer.CustomerID
                    Dim output As New ObservableCollection(Of OrderPreviewModel)
                    For Each item In c
                        output.Add(New OrderPreviewModel With {.CustomerName = item.CustomerNamePreview, .CustomerID = item.CustomerID, .Date_ = item.Datum, .OrderID = item.OrderID, .OrderTyp = item.DokumentArt.ToString, .Value = New Money(item.TotalValueAmount, item.TotalValueCurrency)})
                    Next
                    Return output
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try

        Return New ObservableCollection(Of OrderPreviewModel)
    End Function

    Public Function GetArticle(ArticleID As String, usr As User) As Article Implements IDatabase.GetArticle
        Try
            If usr.CanReadArticle = False Then
                RaiseEvent UserRightsMissing(User.ArticleReadDenied)
                Return New Article
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.ArticleReadDenied)
            Return New Article
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Dim temp = session.Get(Of nHibernate.Models.ArticleModel)(ArticleID)
                    Dim art = temp.ToArticle
                    Return art
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return Nothing
    End Function

    Public Function GetCategories(MainKey As String, usr As User) As Category Implements IDatabase.GetCategories
        Try
            If usr.CanReadArticle = False Then
                RaiseEvent UserRightsMissing(User.ArticleReadDenied)
                Return New Category
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.ArticleReadDenied)
            Return New Category
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Dim item = session.Get(Of Category)(MainKey)
                    Return item
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return Nothing
    End Function

    Public Function GetCustomer(CustomerID As String, usr As User) As Customer Implements IDatabase.GetCustomer
        Try
            If usr.CanReadCustomer = False Then
                RaiseEvent UserRightsMissing(User.CustomerReadDenied)
                Return New Customer
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.CustomerReadDenied)
            Return New Customer
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Return session.Get(Of nHibernate.Models.CustomerModel)(CustomerID).ToCustomer
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return Nothing
    End Function

    Public Function GetOrder(OrderID As String, type As Dokumentart, usr As User) As Order Implements IDatabase.GetOrder
        Try
            If usr.CanReadOrder = False Then
                RaiseEvent UserRightsMissing(User.OrderReadDenied)
                Return New Order
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.OrderReadDenied)
            Return New Order
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Return session.Get(Of nHibernate.Models.OrderModel)(CInt(type).ToString + "-" + OrderID).ToOrder
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return Nothing
    End Function

    Public Function GetOrdersByCustomer(CustomerID As String, usr As User, Optional startdate As Date = Nothing, Optional enddate As Date = Nothing) As IList(Of OrderPreviewModel) Implements IDatabase.GetOrdersByCustomer
        Try
            If usr.CanReadOrder = False Then
                RaiseEvent UserRightsMissing(User.OrderReadDenied)
                Return New Order
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.OrderReadDenied)
            Return New Order
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    If startdate = Nothing AndAlso enddate = Nothing Then
                        Dim c = From orders In session.Query(Of nHibernate.Models.OrderModel)() Where orders.Customer.CustomerID = CustomerID Select orders.CustomerNamePreview, orders.DokumentArt, orders.OrderID, orders.TotalValueAmount, orders.TotalValueCurrency, orders.Datum
                        Dim output As New ObservableCollection(Of OrderPreviewModel)
                        For Each item In c
                            output.Add(New OrderPreviewModel With {.CustomerName = item.CustomerNamePreview, .CustomerID = CustomerID, .Date_ = item.Datum, .OrderID = item.OrderID, .OrderTyp = item.DokumentArt.ToString, .Value = New Money(item.TotalValueAmount, item.TotalValueCurrency)})
                        Next
                        Return output
                    End If
                    If startdate = Nothing AndAlso Not (enddate = Nothing) Then
                        Dim c = From orders In session.Query(Of nHibernate.Models.OrderModel)() Where orders.Customer.CustomerID = CustomerID AndAlso orders.Datum < enddate Select orders.CustomerNamePreview, orders.DokumentArt, orders.OrderID, orders.TotalValueAmount, orders.TotalValueCurrency, orders.Datum
                        Dim output As New ObservableCollection(Of OrderPreviewModel)
                        For Each item In c
                            output.Add(New OrderPreviewModel With {.CustomerName = item.CustomerNamePreview, .CustomerID = CustomerID, .Date_ = item.Datum, .OrderID = item.OrderID, .OrderTyp = item.DokumentArt.ToString, .Value = New Money(item.TotalValueAmount, item.TotalValueCurrency)})
                        Next
                        Return output
                    End If
                    If Not (startdate = Nothing) AndAlso (enddate = Nothing) Then
                        Dim c = From orders In session.Query(Of nHibernate.Models.OrderModel)() Where orders.Customer.CustomerID = CustomerID AndAlso orders.Datum > startdate Select orders.CustomerNamePreview, orders.DokumentArt, orders.OrderID, orders.TotalValueAmount, orders.TotalValueCurrency, orders.Datum
                        Dim output As New ObservableCollection(Of OrderPreviewModel)
                        For Each item In c
                            output.Add(New OrderPreviewModel With {.CustomerName = item.CustomerNamePreview, .CustomerID = CustomerID, .Date_ = item.Datum, .OrderID = item.OrderID, .OrderTyp = item.DokumentArt.ToString, .Value = New Money(item.TotalValueAmount, item.TotalValueCurrency)})
                        Next
                        Return output
                    End If
                    If Not (startdate = Nothing) AndAlso Not (enddate = Nothing) Then
                        Dim c = From orders In session.Query(Of nHibernate.Models.OrderModel)() Where orders.Customer.CustomerID = CustomerID AndAlso orders.Datum > startdate AndAlso orders.Datum < enddate Select orders.CustomerNamePreview, orders.DokumentArt, orders.OrderID, orders.TotalValueAmount, orders.TotalValueCurrency, orders.Datum
                        Dim output As New ObservableCollection(Of OrderPreviewModel)
                        For Each item In c
                            output.Add(New OrderPreviewModel With {.CustomerName = item.CustomerNamePreview, .CustomerID = CustomerID, .Date_ = item.Datum, .OrderID = item.OrderID, .OrderTyp = item.DokumentArt.ToString, .Value = New Money(item.TotalValueAmount, item.TotalValueCurrency)})
                        Next
                        Return output
                    End If
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return New List(Of OrderPreviewModel)
    End Function

    Public Function GetOrdersInInterval(startdate As Date, enddate As Date, usr As User) As IList(Of OrderPreviewModel) Implements IDatabase.GetOrdersInInterval
        Try
            If usr.CanReadOrder = False Then
                RaiseEvent UserRightsMissing(User.OrderReadDenied)
                Return New Order
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.OrderReadDenied)
            Return New Order
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    If Not (startdate = Nothing) AndAlso Not (enddate = Nothing) Then
                        Dim c = From orders In session.Query(Of nHibernate.Models.OrderModel)() Where orders.Datum >= startdate AndAlso orders.Datum <= enddate Select orders.CustomerNamePreview, orders.DokumentArt, orders.OrderID, orders.TotalValueAmount, orders.TotalValueCurrency, orders.Datum, orders.Customer.CustomerID
                        Dim output As New ObservableCollection(Of OrderPreviewModel)
                        For Each item In c
                            output.Add(New OrderPreviewModel With {.CustomerName = item.CustomerNamePreview, .CustomerID = item.CustomerID, .Date_ = New Datum(item.Datum).ToString, .OrderID = item.OrderID, .OrderTyp = item.DokumentArt.ToString, .Value = New Money(item.TotalValueAmount, item.TotalValueCurrency)})
                        Next
                        Return output
                    End If
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return New List(Of OrderPreviewModel)
    End Function

    Public ReadOnly Property ListOfOrderTexts As ObjectModel.ObservableCollection(Of nHibernate.Models.OrderTextModel) Implements IDatabase.ListOfOrderTexts
        Get
            Try
                Using session = sessionFactory.OpenSession()
                    Using transaction = session.BeginTransaction()
                        Dim c = From orders In session.Query(Of nHibernate.Models.OrderTextModel)()
                        Dim output As New ObservableCollection(Of nHibernate.Models.OrderTextModel)
                        For Each item In c
                            output.Add(item)
                        Next
                        Return output
                    End Using
                End Using
            Catch ex As Exception
                RaiseEvent DatabaseError(ex.InnerException.ToString)
            End Try
            Return New ObjectModel.ObservableCollection(Of nHibernate.Models.OrderTextModel)
        End Get
    End Property

    Public ReadOnly Property ListOfPaymentMethodes As ObjectModel.ObservableCollection(Of PaymentMethode) Implements IDatabase.ListOfPaymentMethodes
        Get
            Try
                Using session = sessionFactory.OpenSession()
                    Using transaction = session.BeginTransaction()
                        Dim c = From orders In session.Query(Of nHibernate.Models.PaymentMethodeModel)()
                        Dim output As New ObservableCollection(Of PaymentMethode)
                        For Each item In c
                            output.Add(item.ToPaymentMethode)
                        Next
                        Return output
                    End Using
                End Using
            Catch ex As Exception
                RaiseEvent DatabaseError(ex.InnerException.ToString)
            End Try
            Return New ObjectModel.ObservableCollection(Of PaymentMethode)
        End Get
    End Property

    Public ReadOnly Property ListOfTaxes As ObjectModel.ObservableCollection(Of TaxClass) Implements IDatabase.ListOfTaxes
        Get
            Try
                Using session = sessionFactory.OpenSession()
                    Using transaction = session.BeginTransaction()
                        Dim c = From orders In session.Query(Of TaxClass)()
                        Dim output As New ObservableCollection(Of TaxClass)
                        For Each item In c
                            output.Add(item)
                        Next
                        Return output
                    End Using
                End Using
            Catch ex As Exception
                RaiseEvent DatabaseError(ex.InnerException.ToString)
            End Try
            Return New ObjectModel.ObservableCollection(Of TaxClass)
        End Get
    End Property

    Public ReadOnly Property ListOfUnits As ObjectModel.ObservableCollection(Of Unit) Implements IDatabase.ListOfUnits
        Get
            Try
                Using session = sessionFactory.OpenSession()
                    Using transaction = session.BeginTransaction()
                        Dim c = From orders In session.Query(Of Unit)()
                        Dim output As New ObservableCollection(Of Unit)
                        For Each item In c
                            output.Add(item)
                        Next
                        Return output
                    End Using
                End Using
            Catch ex As Exception
                RaiseEvent DatabaseError(ex.InnerException.ToString)
            End Try
            Return New ObjectModel.ObservableCollection(Of Unit)
        End Get
    End Property

    Public Function LoadTempArticle(usr As User) As IList(Of Article) Implements IDatabase.LoadTempArticle
        'ToDo: Impelement
    End Function

    Public Function LoadTempCustomer(usr As User) As IList(Of Customer) Implements IDatabase.LoadTempCustomer
        'ToDo: Impelement
    End Function

    Public Function LoadTempOrders(usr As User) As IList(Of Order) Implements IDatabase.LoadTempOrders
        'ToDo: Impelement
    End Function

    Public Function OrderExists(OrderID As String, type As Dokumentart, usr As User) As Boolean Implements IDatabase.OrderExists
        Try
            If usr.CanReadOrder = False Then
                RaiseEvent UserRightsMissing(User.OrderReadDenied)
                Return False
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.OrderReadDenied)
            Return False
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Return Not IsNothing(session.Get(Of nHibernate.Models.OrderModel)(CInt(type).ToString + "-" + OrderID))
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return False
    End Function

    Public Sub SaveOrUpdateArticle(article As Article, usr As User) Implements IDatabase.SaveOrUpdateArticle
        Try
            If usr.CanWriteArticle = False Then
                RaiseEvent UserRightsMissing(User.ArticleWriteDenied)
                Exit Sub
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.ArticleWriteDenied)
            Exit Sub
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.SaveOrUpdate(nHibernate.Models.ArticleModel.FromArticle(article))
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Sub SaveOrUpdateCustomer(customer As Customer, usr As User) Implements IDatabase.SaveOrUpdateCustomer
        Try
            If usr.CanWriteCustomer = False Then
                RaiseEvent UserRightsMissing(User.CustomerWriteDenied)
                Exit Sub
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.CustomerWriteDenied)
            Exit Sub
        End Try
        SaveOrUpdatePaymentMethode(Nothing)
        Try
            Dim c = nHibernate.Models.CustomerModel.FromCustomer(customer)
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.SaveOrUpdate(c.Address)
                    session.SaveOrUpdate(c.Contact)
                    Dim a = From ea In session.Query(Of nHibernate.Models.EAddressModel)() Where ea.EAddressID.StartsWith(c.CustomerID + "-")
                    For Each item In a
                        session.Delete(item)
                    Next
                    transaction.Commit()
                End Using
            End Using
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    For Each item In c.Addresses
                        session.SaveOrUpdate(item)
                    Next
                    session.SaveOrUpdate(c)
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Sub SaveOrUpdateOrder(order As Order, usr As User) Implements IDatabase.SaveOrUpdateOrder
        Try
            If usr.CanWriteOrder = False Then
                RaiseEvent UserRightsMissing(User.OrderWriteDenied)
                Exit Sub
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.OrderWriteDenied)
            Exit Sub
        End Try
        Dim o = nHibernate.Models.OrderModel.FromOrder(order)
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    If String.IsNullOrEmpty(order.Customer.CustomerID) Then
                        RaiseEvent DatabaseError("Ein Auftrag muss einem Kunden zugeteilt werden")
                        Exit Sub
                    End If
                    Dim a = From ea In session.Query(Of nHibernate.Models.OrderedArticleModel)() Where ea.OrderArticleIDWithOrderID.StartsWith(o.UniqueID + "-")
                    For Each item In a
                        session.Delete(item)
                    Next
                    Dim b = From ea In session.Query(Of nHibernate.Models.DeliveryAddressModel)() Where ea.GUID.StartsWith(o.UniqueID)
                    For Each item In b
                        session.Delete(item)
                    Next
                    transaction.Commit()
                End Using
            End Using
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    For Each item In o.OrderedArticles
                        session.SaveOrUpdate(item)
                    Next
                    If Not IsNothing(o.DeliveryAddress) Then
                        session.SaveOrUpdate(o.DeliveryAddress)
                    End If
                    session.SaveOrUpdate(o)
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.ToString)
        End Try
    End Sub

    Public Sub SaveOrUpdateOrderText(current As nHibernate.Models.OrderTextModel) Implements IDatabase.SaveOrUpdateOrderText
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.SaveOrUpdate(current)
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Sub SaveOrUpdatePaymentMethode(payment As PaymentMethode) Implements IDatabase.SaveOrUpdatePaymentMethode
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.SaveOrUpdate(nHibernate.Models.PaymentMethodeModel.FromPaymentMethode(payment))
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Sub SaveOrUpdateTax(Tax As TaxClass) Implements IDatabase.SaveOrUpdateTax
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.SaveOrUpdate(Tax)
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Sub SaveOrUpdateUnit(Unit As Unit) Implements IDatabase.SaveOrUpdateUnit
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.SaveOrUpdate(Unit)
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Sub SaveTemps(Orders As IList(Of Order), Customers As IList(Of Customer), Articles As IList(Of Article), usr As User) Implements IDatabase.SaveTemps
        'ToDo: Implement
    End Sub

    Public Sub SetCategory(cat As Category, usr As User) Implements IDatabase.SetCategory
        Try
            If usr.CanWriteArticle = False Then
                RaiseEvent UserRightsMissing(User.ArticleWriteDenied)
                Exit Sub
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.ArticleWriteDenied)
            Exit Sub
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.SaveOrUpdate(cat)
                    If cat.Parent IsNot Nothing Then
                        session.SaveOrUpdate(cat.Parent)
                    End If
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Sub DeleteCategory(cat As Category, usr As User) Implements IDatabase.DeleteCategory
        Try
            If usr.CanWriteArticle = False Then
                RaiseEvent UserRightsMissing(User.ArticleWriteDenied)
                Exit Sub
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.ArticleWriteDenied)
            Exit Sub
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.Delete(cat)
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Event DatabaseError(message As String) Implements IDatabase.DatabaseError

#Region "Application Settings"
    Public Property LastCompany As String Implements IDatabase.LastCompany
        Get
            Dim t = GetSettingByKey("LastCompany")
            If Not t.Equals(_company) Then
                _company = t
                sessionFactory = CreateSessionFactoryWithCompany()
            End If

            Dim cs = GetCompanySettings(t)
            If cs IsNot Nothing Then
                If Not String.IsNullOrEmpty(cs.CompanyID) Then
                    _company = cs.CompanyID
                End If
                Return cs.CompanyName
            Else
                Dim listcs = From list In GetAllCompanys() Where list.CompanyID = t Select list
                Return listcs.First.CompanyName
            End If
        End Get
        Set(ByVal value As String)
            If String.IsNullOrEmpty(_company) Then
                _company = value
                sessionFactory = CreateSessionFactoryWithCompany()
            Else
                Try
                    Dim cs = GetCompanySettings(value)
                    If cs.IsIDNumeric Then
                        value = cs.CompanyID
                    End If
                Catch ex As Exception

                End Try
                
            End If
            SetSettingByKey("LastCompany", value)
        End Set
    End Property

    Public Property LastCustomLayout As String Implements IDatabase.LastCustomLayout
        Get
            Return GetSettingByKey("LastCustomLayout")
        End Get
        Set(value As String)
            SetSettingByKey("LastCustomLayout", value)
        End Set
    End Property

    Public Property TaxDescription As String Implements IDatabase.TaxDescription
        Get
            Return GetSettingByKey("TaxDescription")
        End Get
        Set(value As String)
            SetSettingByKey("TaxDescription", value)
        End Set
    End Property

    Public Sub SetSettingByKey(key As String, value As String) Implements IDatabase.SetSettingByKey
        Try
            Dim setting As New nHibernate.Models.ApplicationSettingsModel With {.AppID = _appid, .Key = key, .Value = value}
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.SaveOrUpdate(setting)
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Function GetSettingByKey(key As String) As String Implements IDatabase.GetSettingByKey
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Try
                        Return session.Get(Of nHibernate.Models.ApplicationSettingsModel)((_appid + "-" + key).ToString).Value
                    Catch ex As Exception
                        Return ""
                    End Try
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.ToString)
        End Try
        Return ""
    End Function
#End Region

#Region "CompanySettings"
    Public Function GetCompanySettings(companyname As String) As nHibernate.Models.CompanySettingsModel Implements IDatabase.GetCompanySettings
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Return session.Get(Of nHibernate.Models.CompanySettingsModel)(companyname)
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return Nothing
    End Function

    Public Sub SaveOrUpdateCompanySettings(settings As nHibernate.Models.CompanySettingsModel) Implements IDatabase.SaveOrUpdateCompanySettings
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    session.SaveOrUpdate(settings)
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub
#End Region
    Public Event UserRightsMissing(message As String) Implements IDatabase.UserRightsMissing

#Region "Not used"
    Public Sub ReloadAllDatabases() Implements IDatabase.ReloadAllDatabases

    End Sub

    Public Sub ReloadArticleDatabase() Implements IDatabase.ReloadArticleDatabase

    End Sub

    Public Sub ReloadCategoryDatabase() Implements IDatabase.ReloadCategoryDatabase

    End Sub

    Public Sub ReloadCompanySettings() Implements IDatabase.ReloadCompanySettings

    End Sub

    Public Sub ReloadCustomerDatabase() Implements IDatabase.ReloadCustomerDatabase

    End Sub

    Public Sub ReloadGlobalSettings() Implements IDatabase.ReloadGlobalSettings

    End Sub

    Public Sub ReloadOrderDatabase() Implements IDatabase.ReloadOrderDatabase

    End Sub

    Public Function Restore() As Boolean Implements IDatabase.Restore

    End Function

    Public Function Backup() As String Implements IDatabase.Backup

    End Function
#End Region

#Region "User"
    Public Function GetAllUsers() As IList(Of nHibernate.Models.UserPreviewModel) Implements IDatabase.GetAllUsers
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Return session.Query(Of nHibernate.Models.UserPreviewModel)().ToList
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return New List(Of nHibernate.Models.UserPreviewModel)
    End Function

    Public Function GetUser(username As String) As nHibernate.Models.UserPreviewModel Implements IDatabase.GetUser
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Return session.Get(Of nHibernate.Models.UserPreviewModel)(username)
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return Nothing
    End Function

    Public Sub SaveOrUpdateUser(user As nHibernate.Models.UserPreviewModel, _currentuser As User) Implements IDatabase.SaveOrUpdateUser
        Try
            If (_currentuser Is Nothing AndAlso GetAllUsers.Count = 0) OrElse _currentuser.CanEditUsers Then
                Using session = sessionFactory.OpenSession()
                    Using transaction = session.BeginTransaction()
                        session.SaveOrUpdate(user)
                        transaction.Commit()
                    End Using
                End Using
            Else
                RaiseEvent UserRightsMissing("Not allowed to add or update users!")
            End If
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
    End Sub

    Public Function UserExists(username As String) As Boolean Implements IDatabase.UserExists
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Return Not IsNothing(session.Get(Of nHibernate.Models.UserPreviewModel)(username))
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try

        Return False
    End Function

    Public Function UserIDExists(userID As String) As Boolean Implements IDatabase.UserIDExists
        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Return Not IsNothing((From user In session.Query(Of nHibernate.Models.UserPreviewModel)() Where user.UserID = userID).SingleOrDefault)
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return False
    End Function
#End Region

    Public Function GetAllCompanys() As IList(Of nHibernate.Models.CompanySettingsModel) Implements IDatabase.GetAllCompanys
        Try
            Dim l As New List(Of nHibernate.Models.CompanySettingsModel)
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Dim q = session.Query(Of nHibernate.Models.CompanySettingsModel)()
                    For Each item In q
                        l.Add(item)
                    Next
                End Using
            End Using
            Return l
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return New List(Of nHibernate.Models.CompanySettingsModel)
    End Function

    Public Function CategoryExists(Key As String, usr As User) As Boolean Implements IDatabase.CategoryExists
        Try
            If usr.CanReadArticle = False Then
                RaiseEvent UserRightsMissing(User.ArticleReadDenied)
                Return False
            End If
        Catch ex As Exception
            RaiseEvent UserRightsMissing(User.ArticleReadDenied)
            Return False
        End Try

        Try
            Using session = sessionFactory.OpenSession()
                Using transaction = session.BeginTransaction()
                    Dim item = session.Get(Of Category)(Key)
                    Return Not IsNothing(item)
                End Using
            End Using
        Catch ex As Exception
            RaiseEvent DatabaseError(ex.InnerException.ToString)
        End Try
        Return False
    End Function


    Public Event DatabaseConnected(source As Object) Implements IDatabase.DatabaseConnected

    Public Event DatabaseNotConnected(source As Object) Implements IDatabase.DatabaseNotConnected

    Public ReadOnly Property SupportsTemporaryItems As Boolean Implements IDatabase.SupportsTemporaryItems
        Get
            Return False
        End Get
    End Property

    Public ReadOnly Property SupportsBackupAndRestore As Boolean Implements IDatabase.SupportsBackupAndRestore
        Get
            Return False
        End Get
    End Property

    Public Function CheckChangedArticles() As IList(Of nHibernate.Models.ArticleModel) Implements IDatabase.CheckChangedArticles

    End Function

    Public Function CheckChangedCustomers() As IList(Of nHibernate.Models.CustomerModel) Implements IDatabase.CheckChangedCustomers

    End Function

    Public Function CheckChangedOrders() As IList(Of nHibernate.Models.OrderModel) Implements IDatabase.CheckChangedOrders

    End Function

    Public ReadOnly Property SupportsSyncing As Boolean Implements IDatabase.SupportsSyncing
        Get
            Return False
        End Get
    End Property
End Class